IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageMove]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageMove]
GO
create procedure [dbo].[cms_sp_PageMove]
(
@PageId [int],
@SiblingPageId [int]
)
as
declare @CurrentPageNodeId hierarchyid
declare @CurrentSiblingPageNodeId hierarchyid
declare @NeighbourPageNodeId hierarchyid
declare @CommonParentPageNodeId hierarchyid
declare @MustBeTheSameParentPageNodeId hierarchyid
declare @NewPageNodeId hierarchyid
declare @SiteId int
declare @SiteGroupId int

select 
	@CurrentPageNodeId = PageNodeId,
	@CommonParentPageNodeId = PageNodeId.GetAncestor(1),
	@SiteId = SiteId,
	@SiteGroupId = SiteGroupId
from dbo.Pages
where PageId = @PageId

select 
	@CurrentSiblingPageNodeId = PageNodeId,
	@MustBeTheSameParentPageNodeId = PageNodeId.GetAncestor(1) 
from dbo.Pages
where PageId = @SiblingPageId and
	((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))

if @CurrentPageNodeId > @CurrentSiblingPageNodeId
begin	
	select @NeighbourPageNodeId = MAX(PageNodeId)
	from dbo.Pages
	where PageNodeId < @CurrentSiblingPageNodeId and
	    @MustBeTheSameParentPageNodeId = PageNodeId.GetAncestor(1) and
		((Pages.SiteGroupId is null and Pages.SiteId is null) or 
		(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
		(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))
		
	set @NewPageNodeId = @CommonParentPageNodeId.GetDescendant(@NeighbourPageNodeId, @CurrentSiblingPageNodeId)
end 
else
begin
	select @NeighbourPageNodeId = MIN(PageNodeId)
	from dbo.Pages
	where PageNodeId > @CurrentSiblingPageNodeId and
	    @MustBeTheSameParentPageNodeId = PageNodeId.GetAncestor(1) and	
		((Pages.SiteGroupId is null and Pages.SiteId is null) or 
		(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
		(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))
		
	set @NewPageNodeId = @CommonParentPageNodeId.GetDescendant(@CurrentSiblingPageNodeId, @NeighbourPageNodeId)
end
if @CommonParentPageNodeId = @MustBeTheSameParentPageNodeId
begin
	update dbo.Pages
		set PageNodeId = PageNodeId.GetReparentedValue(@CurrentPageNodeId, @NewPageNodeId)
	where PageNodeId.IsDescendantOf(@CurrentPageNodeId) = 1 and
		((Pages.SiteGroupId is null and Pages.SiteId is null) or 
		(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
		(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))
end


GO

